import openpyxl
from openpyxl.styles import Font


class Reader:

    def __init__(self):
        self.workbook = None
        self.sheetnames = None
        self.worksheet = None

    def open_excel(self, filename):
        """打开excel文件加载工作薄"""
        self.workbook = openpyxl.load_workbook(filename)
        self.sheetnames = self.workbook.sheetnames
        self.worksheet = self.workbook[self.sheetnames[0]]

    def set_worksheet(self, sheet_name):
        """切换工作表"""
        self.worksheet = self.workbook[sheet_name]

    def read_data_to_list(self):
        """读取当前工作表中的数据为 列表嵌套列表"""
        lines = []  # 表列表, 存储每一行小列表
        for row in list(self.worksheet.rows):
            line = []  # 行列表, 存储行中每个单元格的数据
            for cell in row:
                value = cell.value
                if value is None or value == ' ':
                    value = ''

                line.append(value)  # 每遍历一个单元格 就 追加到行列表中
            lines.append(line)  # 将行列表追到表列表中

        return lines

    def read_data_to_dict(self):
        """读取当前工作表中的数据为 列表嵌套字典"""
        datas = []  # 存储 每行数据的表列表

        title = []  # 第一行数据
        for cell in list(self.worksheet.rows)[0]:  # 从所有行中 获取下标为0的 第一行, 遍历第一行中的单元格
            title.append(cell.value)
        print(title)

        for row in list(self.worksheet.rows)[1:]:  # 从所有行中, 切片获取除第一行外的 其它所有行数据, 遍历每一行
            line = []  # 行列表
            for cell in row:  # 遍历每行中的单元格
                value = cell.value
                if value is None:
                    value = ''
                line.append(value)
            datas.append(dict(zip(title, line)))

        return datas


class Writer:

    def __init__(self):
        self.workbook = None
        self.sheetnames = None
        self.worksheet = None

    def open_excel(self, filename):
        """打开excel文件加载工作薄"""
        self.workbook = openpyxl.load_workbook(filename)
        self.sheetnames = self.workbook.sheetnames
        self.worksheet = self.workbook[self.sheetnames[0]]

    def set_worksheet(self, sheet_name):
        """切换工作表"""
        self.worksheet = self.workbook[sheet_name]

    def write(self, row, col, text, color=None):
        """
        写入指定工作表的 制定行列组成的单元格
        :param row: 行号(从1开始)
        :param col: 列号(从1开始)
        :param text: 写入的数据
         :param color: 0:黑色; 1:白色; 2:红色; 3:绿色; 4:蓝色; 5:黄色;
        :return:
        """
        d = self.worksheet.cell(row, col, text)
        if color:
            if color == '0':
                color = 'FF000000'
            elif color == '1':
                color = 'FFFFFFFF'
            elif color == '2':
                color = 'FFFF0000'
            elif color == '3':
                color = 'FF00FF00'
            elif color == '4':
                color = 'FF0000FF'
            elif color == '5':
                color = 'FFFFFF00'
            else:
                color = 'FF000000'

        font = Font(name='Arial',
                    size=11,
                    bold=False,
                    italic=False,
                    vertAlign=None,
                    underline='none',
                    strike=False,
                    color=color)
        d.font = font

    def save(self, filename):
        """
        写入后保存数据
        :param filename: 保存到指定的文件中
        :return:
        """
        self.workbook.save(filename)

if __name__ == '__main__':
    # 打开文件并加载工作薄
    workbook = openpyxl.load_workbook('../lib/cases.xlsx')
    # 获取工作表 表名
    sheetnames = workbook.sheetnames
    # 切换工作表
    worksheet = workbook[sheetnames[0]]
    # 给指定工作表的 指定行和列中 写入数据; 写入数据时,excel文件不允许为打开状态
    worksheet.cell(2, 8, 'PASS')
    worksheet.cell(2, 9, "测试通过")
    # 保存工作薄
    workbook.save('../lib/cases.xlsx')





